Dataformのコンパイル済みSQLをDataform CLIで作成してSQLをファイルに抽出するスクリプトを作ってみた
2024.10.03概要
DataformでSQLを実装していてBigQueryのアンチパターンになっていないかを確認したい、そう思ったのがこの記事のきっかけでした。
BigQuery のアンチパターンSQLを確認できるツールがあります。
このツールにDataformのSQLを噛ませるには
- 拡張子を
.sqlxから.sqlに変換する - configなどSQL以外のものを削除しなくてはいけない
などなかなか大変です。というかDataformのSQLXファイルならコンパイルしないと無理です。
そこで今回はDataform CLIを用いてローカル環境でコンパイルして、そのコンパイル結果からSQLを抽出するスクリプトを作ってみました。
興味がある方は読んでみてください。
それでは本編
まずは流れを説明します。
- コンパイル前のソースコード入手
- コンパイルを実行して実行結果をファイルに出力
- ファイルをスクリプトで処理
という流れになります。
コンパイル前のソースコード入手
※Dataform CLIの環境を整えてください。
この記事ではインストール方法などは解説しないので以下のリファレンスをご参照ください。
Dataform CLIをインストールできたらDataformプロジェクトをローカルに用意します。
GitHubなどのサードパーティのリポジトリで管理しているかたはPULLしてローカルに持ってきてください。
ローカルに持ってきたら、
dataform init
を実行します。
サードパーティのリポジトリを用いていない方(DataformのGit利用者)は、、、
以下の記事をもとにローカル(またはCloud Shellなど)に持ってきてください。
ワークスペースから持ってきた場合はdataform initは不要です。
ソースコードが手に入ったら準備は完了です。
コンパイル実行
Dataformプロジェクト直下(package.jsonなどがある階層)で以下のコマンドを実行します。
dataform compile --json > input.json
コンパイルを行うコマンドで、出力をjson形式に指定しています。
またその結果をinput.jsonというファイルにリダイレクトしています。
input.jsonは今回作成したシェルスクリプトで使用しているファイル名というだけですのでシェルスクリプト内の変数を書き換えれば別の名前でも問題ないです。
上記コマンドを実行するとコンパイル済みのSQLを含んだjsonが出力されます。
今回はどんな出力されるのか確認のため3種類のテーブル型定義(table view operations)を用いたSQLXファイルを作成してコンパイルしてみました。
config { type: "operations" }
SELECT 1 FROM test
config {type: "view"}
SELECT 2 FROM test3
config {type: "table"}
SELECT 3 FROM test3
出力は以下でした。
{
"tables": [
{
"type": "table",
"target": {
"schema": "dataform",
"name": "test3",
"database": "testtest"
},
"query": "\n\nSELECT 3\n",
"disabled": false,
"fileName": "definitions/test3.sqlx",
"canonicalTarget": {
"schema": "dataform",
"name": "test3",
"database": "testtest"
},
"enumType": "TABLE"
}
],
"operations": [
{
"target": {
"schema": "dataform",
"name": "test",
"database": "testtest"
},
"queries": [
"\nSELECT 1\n"
],
"fileName": "definitions/test.sqlx",
"hermeticity": "NON_HERMETIC",
"canonicalTarget": {
"schema": "dataform",
"name": "test",
"database": "testtest"
}
},
{
"target": {
"schema": "dataform",
"name": "view",
"database": "testtest"
},
"queries": [
"{config type:view}\nSELECT 2\n"
],
"fileName": "definitions/view.sqlx",
"hermeticity": "NON_HERMETIC",
"canonicalTarget": {
"schema": "dataform",
"name": "view",
"database": "testtest"
}
}
],
・・・以下省略
}
tableの場合query、view・operationsの場合はqueriesでSQLを取得することができるようです。
スクリプト全文です
jqコマンドメインでjsonをパースするシェルスクリプトを実装してみました。
全文は以下です。
#!/bin/bash
# JSONファイルのパスを指定
json_file="input.json"
# 出力ディレクトリを指定
output_dir="compiled_sql_dir"
# 出力ディレクトリが存在しない場合は作成
mkdir -p "$output_dir"
# tablesの処理
jq -c '.tables[] | {fileName: .fileName, query: .query}' "$json_file" | while read -r item; do
# jqの出力を解析
fileName=$(echo "$item" | jq -r '.fileName')
query=$(echo "$item" | jq -r '.query')
# 元のディレクトリ構造を維持するためのパスを作成
relativePath="${fileName%.sqlx}_compiled.sql"
newFileName="${output_dir}/${relativePath}"
newDir=$(dirname "$newFileName")
# 必要なディレクトリを作成
mkdir -p "$newDir"
# 一時ファイルにクエリを保存
tmp_file=$(mktemp)
echo "$query" > "$tmp_file"
# 一時ファイルから最終ファイルに移動
mv "$tmp_file" "$newFileName"
done
# operationsの処理
jq -c '.operations[] | {fileName: .fileName, queries: .queries}' "$json_file" | while read -r item; do
# jqの出力を解析
fileName=$(echo "$item" | jq -r '.fileName')
queries=$(echo "$item" | jq -r '.queries[]')
# 元のディレクトリ構造を維持するためのパスを作成
relativePath="${fileName%.sqlx}_compiled.sql"
newFileName="${output_dir}/${relativePath}"
newDir=$(dirname "$newFileName")
# 必要なディレクトリを作成
mkdir -p "$newDir"
# 一時ファイルにクエリを保存
tmp_file=$(mktemp)
echo "$queries" > "$tmp_file"
# 一時ファイルから最終ファイルに移動
mv "$tmp_file" "$newFileName"
done
echo "Compiled SQL queries have been saved."
簡単に解説します。
JSONファイルと出力ディレクトリの指定:
json_file="input.json"
output_dir="compiled_sql_dir"
json_file変数にJSONファイルのパスを指定します。
output_dir変数に出力ディレクトリの名前を指定します。
出力ディレクトリの作成:
mkdir -p "$output_dir"
mkdir -pコマンドを使用して、出力ディレクトリが存在しない場合は作成します。
tablesの処理:
jq -c '.tables[] | {fileName: .fileName, query: .query}' "$json_file" | while read -r item; do
# jqの出力を解析
fileName=$(echo "$item" | jq -r '.fileName')
query=$(echo "$item" | jq -r '.query')
# 元のディレクトリ構造を維持するためのパスを作成
relativePath="${fileName%.sqlx}_compiled.sql"
newFileName="${output_dir}/${relativePath}"
newDir=$(dirname "$newFileName")
# 必要なディレクトリを作成
mkdir -p "$newDir"
# 一時ファイルにクエリを保存
tmp_file=$(mktemp)
echo "$query" > "$tmp_file"
# 一時ファイルから最終ファイルに移動
mv "$tmp_file" "$newFileName"
done
jqコマンドを使用して、tables配列内の各オブジェクトからファイル名とSQLを抽出します。
一時ファイルにクエリを保存し、それを最終的なファイルに移動します(改行コードなどの特殊文字をうまく処理するため)。
operationsの処理:
jq -c '.operations[] | {fileName: .fileName, queries: .queries}' "$json_file" | while read -r item; do
# jqの出力を解析
fileName=$(echo "$item" | jq -r '.fileName')
queries=$(echo "$item" | jq -r '.queries[]')
# 元のディレクトリ構造を維持するためのパスを作成
relativePath="${fileName%.sqlx}_compiled.sql"
newFileName="${output_dir}/${relativePath}"
newDir=$(dirname "$newFileName")
# 必要なディレクトリを作成
mkdir -p "$newDir"
# 一時ファイルにクエリを保存
tmp_file=$(mktemp)
echo "$queries" > "$tmp_file"
# 一時ファイルから最終ファイルに移動
mv "$tmp_file" "$newFileName"
done
jqコマンドを使用して、operations配列内の各オブジェクトからファイル名とSQLを抽出します。
一時ファイルにクエリを保存し、それを最終的なファイルに移動します(改行コードなどの特殊文字をうまく処理するため)。
※tableとoperationsで似通った処理になってるので気になる方は共通化してみてください。
このシェルスクリプトを、input.jsonと同じ階層で実行すると
compiled_sql_dirというディレクトリが作成されて、その配下にSQLファイルがDataoformプロジェクトと同じディレクトリ構成で作成されます。
まとめ
コンパイル済みSQLを入手できればあとはアンチパターンのツールにかけることも、bqコマンドでBigQueryにSQLを発行することも可能です。好きなように料理することができると思います。
今回はDataform CLIとシェルスクリプトでコンパイルSQLを作成しましたが、他にも様々なやり方があると思います。
また他のやり方を見つけたら記事にしようと思います。
それではまた。ナマステー
参考
![[アップデート]Cloud Run jobsのタスクのタイムアウト時間上限が7日間になりました(プレビュー)](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-5fe8bdb7ead2540607eb6dea227e3831/651e7ec33f466f7b022dd176c67936f9/googlecloud-cloud-run)
